﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;

using FundSms.Client.IDAL;
using FundSms.Client.Model;
using FundSms.DBUtility;

namespace FundSms.Client.OracleDAL
{
    public class SmsSendList : ISmsSendList
    {
        /// <summary>
        /// 选择待发数据
        /// </summary>
        private const string SQL_SELECT_SENDLIST = "SELECT L_SERIALNO,nvl(C_MOBILENO,'') AS C_MOBILENO,nvl(C_CONTENT,'') AS C_Content,NVL(C_PROPERTY,'0') AS C_PROPERTY,NVL(c_from,'') as c_from,nvl(c_smsname,'') as c_smsname,nvl(c_smsid,'') as c_smsid,nvl(c_date,sysdate) as c_date,nvl(c_r1,'') as c_r1,nvl(c_r2,'') as c_r2,nvl(c_r3,'') as c_r3,nvl(c_r4,'') as c_r4,nvl(c_r5,'') as c_r5 from tsms_send_hundsun ORDER BY C_PROPERTY,L_SERIALNO";
        private const string SQL_SELECT_SENDLIST_TOP = "SELECT L_SERIALNO,nvl(C_MOBILENO,'') AS C_MOBILENO,nvl(C_CONTENT,'') AS C_Content,NVL(C_PROPERTY,'0') AS C_PROPERTY,NVL(c_from,'') as c_from,nvl(c_smsname,'') as c_smsname,nvl(c_smsid,'') as c_smsid,nvl(c_date,sysdate) as c_date,nvl(c_r1,'') as c_r1,nvl(c_r2,'') as c_r2,nvl(c_r3,'') as c_r3,nvl(c_r4,'') as c_r4,nvl(c_r5,'') as c_r5 from tsms_send_hundsun WHERE rownum <= :RowNumber ORDER BY C_PROPERTY,L_SERIALNO";
        private const string SQL_SELECT_SENDLIST_TOP_MOD = "SELECT L_SERIALNO,nvl(C_MOBILENO,'') AS C_MOBILENO,nvl(C_CONTENT,'') AS C_Content,NVL(C_PROPERTY,'0') AS C_PROPERTY,NVL(c_from,'') as c_from,nvl(c_smsname,'') as c_smsname,nvl(c_smsid,'') as c_smsid,nvl(c_date,sysdate) as c_date,nvl(c_r1,'') as c_r1,nvl(c_r2,'') as c_r2,nvl(c_r3,'') as c_r3,nvl(c_r4,'') as c_r4,nvl(c_r5,'') as c_r5 from tsms_send_hundsun WHERE rownum <= :RowNumber AND MOD(L_SERIALNO,:Count) = :Mod ORDER BY C_PROPERTY,L_SERIALNO";
        private const string SQL_SELECT_SENDLIST_SINGLE = "SELECT L_SERIALNO,nvl(C_MOBILENO,'') AS C_MOBILENO,nvl(C_CONTENT,'') AS C_Content,NVL(C_PROPERTY,'0') AS C_PROPERTY,NVL(c_from,'') as c_from,nvl(c_smsname,'') as c_smsname,nvl(c_smsid,'') as c_smsid,nvl(c_date,sysdate) as c_date,nvl(c_r1,'') as c_r1,nvl(c_r2,'') as c_r2,nvl(c_r3,'') as c_r3,nvl(c_r4,'') as c_r4,nvl(c_r5,'') as c_r5 from tsms_send_hundsun WHERE L_SERIALNO = :ID ";
        private const string SQL_SELECT_UNSEND_COUNT = "SELECT COUNT(1) FROM tsms_send_hundsun";

        /// <summary>
        /// 删除数据
        /// </summary>
        private const string SQL_DELETE = "DELETE FROM TSMS_SEND_HUNDSUN";
        private const string SQL_DELETE_SINGLE = "DELETE FROM TSMS_SEND_HUNDSUN WHERE L_SERIALNO = :ID";
        private const string SQL_DELETE_LIST = "DELETE FROM TSMS_SEND_HUNDSUN WHERE L_SERIALNO IN (:IDList)";

        /// <summary>
        /// 参数变量
        /// </summary>
        private const string PARM_ROW_NUM = ":RowNumber";
        private const string PARM_COUNT = ":Count";
        private const string PARM_MOD = ":Mod";
        private const string PARM_ID = ":ID";
        private const string PARM_ID_LIST = ":IDList";

        #region ISmsSendList 成员

        public IList<TSMSSendHundSunInfo> GetSendList()
        {
            //定义一个返回列表
            IList<TSMSSendHundSunInfo> listall = new List<TSMSSendHundSunInfo>();
            //执行查询
            using(OracleDataReader reader = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringClientSms,CommandType.Text,SQL_SELECT_SENDLIST,null))
            {
                //遍历结果集
                while(reader.Read())
                {
                    TSMSSendHundSunInfo sendinfo = new TSMSSendHundSunInfo(reader.GetInt32(0),reader.GetString(1),reader.GetString(2),reader.GetString(3),reader.GetString(4),reader.GetString(5),reader.GetString(6),reader.GetDateTime(7),reader.GetValue(8).ToString(),reader.GetValue(9).ToString(),reader.GetValue(10).ToString(),reader.GetValue(11).ToString(),reader.GetValue(12).ToString());

                    listall.Add(sendinfo);
                }

                reader.Close();
            }

            return listall;
        }

        public IList<TSMSSendHundSunInfo> GetSendList(int rownum)
        {
            //定义一个返回列表
            IList<TSMSSendHundSunInfo> listall = new List<TSMSSendHundSunInfo>();

            //数据参数
            OracleParameter parm = new OracleParameter(PARM_ROW_NUM, OracleType.Int32, 4);
            //设值
            parm.Value = rownum;

            //执行查询
            using (OracleDataReader reader = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_SENDLIST_TOP, parm))
            {
                //遍历结果集
                while(reader.Read())
                {
                    TSMSSendHundSunInfo sendinfo = new TSMSSendHundSunInfo(reader.GetInt32(0),reader.GetString(1),reader.GetString(2),reader.GetString(3),reader.GetString(4),reader.GetString(5),reader.GetString(6),reader.GetDateTime(7),reader.GetValue(8).ToString(),reader.GetValue(9).ToString(),reader.GetValue(10).ToString(),reader.GetValue(11).ToString(),reader.GetValue(12).ToString());

                    listall.Add(sendinfo);
                }

                reader.Close();
            }

            return listall;
        }

        public IList<TSMSSendHundSunInfo> GetSendList(int rownum,int count,int mod)
        {
            //定义一个返回列表
            IList<TSMSSendHundSunInfo> listall = new List<TSMSSendHundSunInfo>();

            OracleParameter[] parms = GetSendListParameters();
            parms[0].Value = rownum;
            parms[1].Value = count;
            parms[2].Value = mod;

            //执行查询
            using (OracleDataReader reader = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_SENDLIST_TOP_MOD, parms))
            {
                //遍历结果集
                while (reader.Read())
                {
                    TSMSSendHundSunInfo sendinfo = new TSMSSendHundSunInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetDateTime(7), reader.GetValue(8).ToString(), reader.GetValue(9).ToString(), reader.GetValue(10).ToString(), reader.GetValue(11).ToString(), reader.GetValue(12).ToString());

                    listall.Add(sendinfo);
                }

                reader.Close();
            }

            return listall;
        }

        public TSMSSendHundSunInfo GetSms(int id)
        {
            TSMSSendHundSunInfo sendinfo = null;

            //数据参数
            OracleParameter parm = new OracleParameter(PARM_ID, OracleType.Int32, 4);
            //设值
            parm.Value = id;

            //执行查询
            using (OracleDataReader reader = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_SENDLIST_SINGLE, parm))
            {
                //遍历结果集
                if (reader.Read())
                {
                    sendinfo = new TSMSSendHundSunInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetDateTime(7), reader.GetValue(8).ToString(), reader.GetValue(9).ToString(), reader.GetValue(10).ToString(), reader.GetValue(11).ToString(), reader.GetValue(12).ToString());
                }

                reader.Close();
            }

            return sendinfo;
        }

        public void DeleteSms()
        {
            OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_DELETE, null);
        }

        public void DeleteSms(int id)
        {
            //数据参数
            OracleParameter parm = new OracleParameter(PARM_ID, OracleType.Int32, 4);
            //设值
            parm.Value = id;

            OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_DELETE_SINGLE, parm);
        }

        public void DeleteSmsList(string idlist)
        {
            //数据参数
            OracleParameter parm = new OracleParameter(PARM_ID_LIST, OracleType.Int32, 4);
            //设值
            parm.Value = idlist;

            OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_DELETE_LIST, parm);
        }

        public int GetUnSendCount()
        {
            int result = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_UNSEND_COUNT, null);

            return result;
        }

        #endregion

        /// <summary>
        /// 获得缓存的参数
        /// </summary>
        /// <returns></returns>
        private static OracleParameter[] GetSendListParameters()
        {
            OracleParameter[] parms = OracleHelper.GetCachedParameters(SQL_SELECT_SENDLIST_TOP_MOD);

            if (parms == null)
            {
                parms = new OracleParameter[] {												 
					new OracleParameter(PARM_ROW_NUM, OracleType.Int32, 4),
					new OracleParameter(PARM_COUNT, OracleType.Int32, 4),
					new OracleParameter(PARM_MOD, OracleType.Int32,4)
                };

                OracleHelper.CacheParameters(SQL_SELECT_SENDLIST_TOP_MOD, parms);
            }

            return parms;
        }
    }
}
